# -*- coding:utf-8 -*-
import datetime
import sys
import time

import xlrd
import ibm_db

##程序执行后会自动往表里面插入缺失的tag点数据项，但是分组不会自动插入，可以根据输出的提示手动到xCyberMainTable设置的表里面去添加或者删除对应的分组
# 程序不会对数据库中的数据进行删除
# 插入前程序会自动将数据库中的相关的tag点数据备份到文件中文件名为执行的时间戳
# 当前使用的excel文件处理库只支持xls格式的文件
# excel路径
excelPath = "C:\\Users\\DingMiaoBin\\Documents\\资料\\热轧\\股份热轧\\H031_SignalTag_List.xls"
groupSheetName = "Content"
# 数据库配置
dbConfig = {
    "host": "160.0.3.50",
    "port": 50000,
    "database": "hotmill2",
    "user": "ap",
    "pwd": "123456",
    "schema": "IPLAT4J"
}
# \u50A8\u5B58\u6811\u8282\u70B9\u6570\u636E
xCyberTreeTable = "H031.T_TREE_TREENODE"
# \u50A8\u5B58\u6811\u7ED3\u6784
xCyberMainTable = "H031.T_TREE_MAINNODE"
# 服务配置
treeName = "simultaneoustree";
rootTreeId = "2050";

if __name__ == '__main__':
    print("start");
    wb = xlrd.open_workbook(excelPath)
    # 通过Book对象的sheet_names方法可以获取所有表单名称
    sheetnames = wb.sheet_names()
    print(sheetnames)
    groupSheet = wb.sheet_by_name(groupSheetName);
    childSheetNameList = [];
    childSheetContent = {};
    parentSheetMap = {};
    for row in range(groupSheet.nrows):
        for col in range(groupSheet.ncols):
            cell = groupSheet.cell(row, col)
            value = cell.value
            if value != '':
                # 获取链接
                superLink = groupSheet.hyperlink_map.get((row, col));
                if superLink != None:
                    # print("row:{},col:{},val:{},superLink:{}".format(row,col,value,superLink));
                    textmark = superLink.textmark;
                    realSheetName = textmark[0:textmark.index('!')].replace("'", "")
                    childSheetNameList.append({
                        "desc": cell.value,
                        "sheetName": realSheetName,
                        "contentSheetName": value,
                        "col": col
                    });
                elif value in sheetnames:
                    childSheetNameList.append({
                        "desc": cell.value,
                        "sheetName": value,
                        "contentSheetName": value,
                        "col": col
                    });
                else:
                    parentSheetMap[col] = value;
    firstRowCache = None;
    for sheetDesc in childSheetNameList:
        sheetname = sheetDesc["desc"];
        realSheetName = sheetDesc["sheetName"];
        sheet = wb.sheet_by_name(realSheetName);
        sheetColNameList = [];
        childSheetContent[sheetname] = [];
        firstRow = sheet.row(0);
        if firstRowCache == None:
            firstRowCache = firstRow;
        else:
            if str(firstRowCache) != str(firstRow):
                raise ValueError("sheet:{} 列头与其他sheet页不相同".format(sheetname));
        for cell in firstRow:
            sheetColNameList.append(cell.value);
        for row in range(1, sheet.nrows):
            childSheetContent[sheetname].append({});
            for col in range(len(sheetColNameList)):
                cell = sheet.cell(row, col);
                childSheetContent[sheetname][row - 1][sheetColNameList[col]] = cell.value;
    # print(childSheetContent);
    # 转换格式
    for excelParentKey in childSheetContent:
        for i in range(len(childSheetContent[excelParentKey])):
            originalExcelElement = childSheetContent[excelParentKey][i];
            newExcelElement = {
                "SHOWNAME": originalExcelElement["Tag  Symbol"],
                "PARENTID": excelParentKey,
                "SOURCE": "iHyperDB",
                "TAGNAME": originalExcelElement["Tag  Symbol"],
                "DESC": originalExcelElement["Remark"],
                "UNIT": originalExcelElement["Unit"],
                "TREENAME": treeName
            }
            childSheetContent[excelParentKey][i] = newExcelElement;

    # print(childSheetContent)
    # 链接数据库
    dbUrl = "DATABASE={};SCHEMA={};HOSTNAME={};PORT={};PROTOCOL=TCPIP;UID={};PWD={};".format(
        dbConfig["database"],
        dbConfig["schema"],
        dbConfig["host"],
        dbConfig["port"],
        dbConfig["user"],
        dbConfig["pwd"]
    )
    conn = ibm_db.connect(dbUrl, "", "");
    # 关闭自动提交
    ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
    # 查询
    select_sql = "select SHOWNAME,PARENTID,SOURCE,TAGNAME,DESC,UNIT FROM {}".format(xCyberTreeTable);
    stmt = ibm_db.exec_immediate(conn, select_sql)
    res = ibm_db.fetch_assoc(stmt)
    count = 0;
    queryResult = {};
    resCache = []
    while res:
        res = ibm_db.fetch_assoc(stmt)
        if not res:
            break;
        resCache.append(res);
        if not queryResult.__contains__(res["PARENTID"]):
            queryResult[res["PARENTID"]] = []
        queryResult[res["PARENTID"]].append(res);
    # 备份数据库
    resCachePath = sys.path[0] + "\\resCache" + xCyberTreeTable + time.strftime('%Y%m%d%H%M%S',
                                                                                time.localtime(time.time())) + ".txt"

    f = open(resCachePath, 'w');
    f.write(str(resCache));
    f.close();
    # 校验excel内容与sql结果,并插入数据库

    for excelParentKey in childSheetContent:
        if not queryResult.__contains__(excelParentKey):
            queryResult[excelParentKey] = [];
        excelNameList = map(lambda x: x['SHOWNAME'], childSheetContent[excelParentKey]);
        dbNameList = map(lambda x: x['SHOWNAME'], queryResult[excelParentKey]);
        differNameList = set(excelNameList).difference(set(dbNameList))
        for insertElement in childSheetContent[excelParentKey]:
            tagName = insertElement['SHOWNAME'];
            if differNameList.__contains__(tagName):
                insert_sql = "INSERT into {} (SHOWNAME,PARENTID,SOURCE,TAGNAME,DESC,UNIT,TREENAME) VALUES ('{}','{}','{}','{}','{}','{}','{}')" \
                    .format(xCyberTreeTable, insertElement['SHOWNAME'], insertElement['PARENTID'],
                            insertElement['SOURCE'], insertElement['TAGNAME'], insertElement['DESC'],
                            insertElement['UNIT'], insertElement["TREENAME"])
                print(insert_sql)
                ibm_db.exec_immediate(conn, insert_sql);
    ibm_db.commit(conn);
    # 验证分组是否缺少
    mainTableResult = []
    select_sql = "select ID,PARENTID FROM {} where TREENAME='{}'".format(xCyberMainTable,treeName);
    stmt = ibm_db.exec_immediate(conn, select_sql)
    res = ibm_db.fetch_assoc(stmt)
    while res:
        res = ibm_db.fetch_assoc(stmt)
        if not res:
            break;
        mainTableResult.append(res["ID"]);
    lackgroup = []
    for childSheet in childSheetNameList:
        if not mainTableResult.__contains__(childSheet["contentSheetName"]):
            lackgroup.append(childSheet["contentSheetName"]);
    if len(lackgroup) > 0:
        print("分组" + str(lackgroup) + "缺失")
        print("推荐执行以下sql");
        templateSql = "INSERT INTO {} (ID,PARENTID,SHOWNAME,TREENAME)VALUES('{}','{}','{}','{}');";
        for groupName in lackgroup:
            find = filter(lambda x: x["contentSheetName"] == groupName, childSheetNameList);
            sheetInfo = find.__next__();
            sql = templateSql.format(xCyberMainTable, groupName, parentSheetMap[sheetInfo["col"]], groupName, treeName)
            print(sql);
        for groupIndex in parentSheetMap:
            groupName=parentSheetMap[groupIndex];
            sql = templateSql.format(xCyberMainTable, groupName, rootTreeId, groupName, treeName)
            print(sql);